with unpcakage_detail as (
    select
        t1.billcode,
        t1.scan_time,
        t1.scan_type,
        t2.center_code,
        t2.center_name,
        t2.schedule_code,
        t2.schedule_name,
        t2.schedule_start_time ,
        t2.schedule_end_time ,
        t2.create_day,
        --t2.employ_code,
-- t2.employ_name,
        concat_ws(',',t2.linked_method) as linked_method,--方法
        --if(t2.sign_in_time is not null and  t2.sign_out_time is not null,1,0) as is_sign,
        t2.create_day  as date_time,
        --t2.sn_code as sn_code,
        --t2.create_day as dt,
        row_number() over(partition by t2.center_code,t2.schedule_code order by t1.billcode ) rk,
            t1.package_code --包号
    from
--班次开始和结束时间内所有操作单号，提出班次内操作单号
(
    select
        billcode, scan_time, inputsite, scansite_code, scanuser, scanuser_code, scan_type, update_time, sn_code, dt,package_code
    from jms_dm.dm_employee_operate_num_all_detail
    where dt=date_add('{{ execution_date | cst_ds }}',-1) and scan_type  ='转运中心拆包'
)t1 inner join
(
    select center_code,center_name,schedule_code,schedule_name,schedule_start_time,schedule_end_time,create_day,
           collect_set(linked_method) linked_method,max(sign_in_time) sign_in_time,max(sign_out_time) sign_out_time
    from  jms_dm.dm_employee_operate_info_detail
    where dt=date_add('{{ execution_date | cst_ds }}',-1) and linked_method ='转运中心拆包' and unpackflag_e=1
    group by center_code,center_name,schedule_code,schedule_name,schedule_start_time,schedule_end_time,create_day
)t2
on t1.scansite_code=t2.center_code           --组内计算是根据扫描时间和中心编码
    and t1.scan_time >=t2.schedule_start_time
    and t1.scan_time <=t2.schedule_end_time
--以上取到转运中心拆包所有单号记录
    left join
(  select billcode,center_code
   from  jms_dm.dm_employee_operate_num_billcode_detail
   where dt=date_add('{{ execution_date | cst_ds }}',-1) and scan_type ='转运中心拆包'
   group by  billcode,center_code
)t3 on t1.scansite_code=t3.center_code
    and t1.billcode =t3.billcode
    where t3.billcode is null
--剔除组内单号
)


insert overwrite table jms_dm.dm_employee_operate_billcode_detail_out_dt partition(dt)
select
    t1.billcode,
    t1.scan_time,
    t1.scan_type,
    t2.center_code,
    t2.center_name,
    t2.schedule_code,
    t2.schedule_name,
    t2.schedule_start_time ,
    t2.schedule_end_time ,
    t2.create_day,
    t2.employ_code,
    t2.employ_name,
    concat_ws(',',t2.linked_method) as linked_method,--方法
    if(t2.sign_in_time is not null and  t2.sign_out_time is not null,1,0) as is_sign,
    t2.create_day  as date_time,
    '' as sn_code
    , if(t1.scan_type in ('转运中心装车','转运中心卸车') and t1.package_code is not null,0,1) as is_pieces   --包+单件,件数
    , if(t1.scan_type in ('转运中心装车','转运中心卸车') and substr(t1.billcode,1,1)='B',0,1) as is_ticket   --包内件+单间,票数
    , t1.package_code
    , date(t2.create_day) as dt
from
--装车卸车建包：班次开始和结束时间内所有操作单号，剔除班次内操作单号（剔除员工编码、单号一一对应的）
    (
    select
    billcode, scan_time, inputsite, scansite_code, scanuser, scanuser_code, scan_type, update_time, sn_code, dt,package_code
    from jms_dm.dm_employee_operate_num_all_detail
    where dt=date_add('{{ execution_date | cst_ds }}',-1) and scan_type in ('转运中心装车','转运中心卸车','转运中心建包')
    )t1 inner join
    (
    select center_code,center_name,schedule_code,schedule_name,schedule_start_time,schedule_end_time,employ_code,employ_name,create_day,
    collect_set(linked_method) linked_method,max(sign_in_time) sign_in_time,max(sign_out_time) sign_out_time
    from  jms_dm.dm_employee_operate_info_detail
    where dt=date_add('{{ execution_date | cst_ds }}',-1) and linked_method in ('转运中心装车','转运中心卸车','转运中心建包')
    group by center_code,center_name,schedule_code,schedule_name,schedule_start_time,schedule_end_time,employ_code,employ_name,create_day
    )t2
on t1.scansite_code=t2.center_code
    and t1.scanuser_code=t2.employ_code
    and t1.scan_time >=t2.schedule_start_time
    and t1.scan_time <=t2.schedule_end_time
--以上是取到
    left join
    (  select billcode,center_code,employ_code
    from  jms_dm.dm_employee_operate_num_billcode_detail
    where dt=date_add('{{ execution_date | cst_ds }}',-1) and scan_type in ('转运中心装车','转运中心卸车','转运中心建包')
    group by  billcode,center_code,employ_code
    )t3 on t1.scansite_code=t3.center_code
    and t1.scanuser_code=t3.employ_code
    and t1.billcode =t3.billcode
where t3.billcode is null

union all
---供件台扫描：班次开始和结束时间内所有操作单号，剔除班次内操作单号（剔除员工编码、设备编码、单号一一对应的）
select
    t1.billcode,
    t1.scan_time,
    t1.scan_type,
    t2.center_code,
    t2.center_name,
    t2.schedule_code,
    t2.schedule_name,
    t2.schedule_start_time ,
    t2.schedule_end_time ,
    t2.create_day,
    t2.employ_code,
    t2.employ_name,
    concat_ws(',',t2.linked_method) as linked_method,--方法
    if(t2.sign_in_time is not null and  t2.sign_out_time is not null,1,0) as is_sign,
    t2.create_day  as date_time,
    t2.sn_code as sn_code
   , if(t1.scan_type in ('转运中心装车','转运中心卸车') and t1.package_code is not null,0,1) as is_pieces   --包+单件,件数
   , if(t1.scan_type in ('转运中心装车','转运中心卸车') and substr(t1.billcode,1,1)='B',0,1) as is_ticket   --包内件+单间,票数
   , t1.package_code
   , date(t2.create_day) as dt
from
    (
    select
    billcode, scan_time, inputsite, scansite_code, scanuser, scanuser_code, scan_type, update_time, sn_code, dt,package_code
    from jms_dm.dm_employee_operate_num_all_detail
    where dt=date_add('{{ execution_date | cst_ds }}',-1) and scan_type ='供件台扫描'
    )t1 inner join
    (
    select center_code,center_name,schedule_code,schedule_name,schedule_start_time,schedule_end_time,employ_code,employ_name,create_day,sn_code,
    collect_set(linked_method) linked_method,max(sign_in_time) sign_in_time,max(sign_out_time) sign_out_time
    from  jms_dm.dm_employee_operate_info_detail
    where dt=date_add('{{ execution_date | cst_ds }}',-1) and linked_method ='供件台扫描'
    group by center_code,center_name,schedule_code,schedule_name,schedule_start_time,schedule_end_time,employ_code,employ_name,create_day,sn_code
    )t2
on t1.scansite_code=t2.center_code
    and (t1.scanuser_code=t2.employ_code or t1.sn_code=t2.sn_code)
    and t1.scan_time >=t2.schedule_start_time
    and t1.scan_time <=t2.schedule_end_time
    left join
    (  select billcode,center_code,employ_code,sn_code
    from  jms_dm.dm_employee_operate_num_billcode_detail
    where dt=date_add('{{ execution_date | cst_ds }}',-1) and scan_type ='供件台扫描'
    group by  billcode,center_code,employ_code,sn_code
    )t3 on t1.scansite_code=t3.center_code
    and (t1.scanuser_code=t3.employ_code or t1.sn_code=t3.sn_code)
    and t1.billcode =t3.billcode
where t3.billcode is null

union all
---转运中心拆包：将班次时间内单号平均复制给扫描员
select
    t1.billcode,
    t1.scan_time,
    t1.scan_type,
    t1.center_code,
    t1.center_name,
    t1.schedule_code,
    t1.schedule_name,
    t1.schedule_start_time ,
    t1.schedule_end_time ,
    t1.create_day,
    t2.employ_code,
    t2.employ_name,
    concat_ws(',',t2.linked_method) as linked_method,--方法
    if(t2.sign_in_time is not null and  t2.sign_out_time is not null,1,0) as is_sign,
    t1.create_day  as date_time,
    t2.sn_code as sn_code
    , if(t1.scan_type in ('转运中心装车','转运中心卸车') and t1.package_code is not null,0,1) as is_pieces   --包+单件,件数
    , if(t1.scan_type in ('转运中心装车','转运中心卸车') and substr(t1.billcode,1,1)='B',0,1) as is_ticket   --包内件+单间,票数
    , t1.package_code
    , date(t1.create_day) as dt
from unpcakage_detail t1
    left join
    ( select * ,
    count(employ_code)  over(partition by center_code,schedule_code) ecnt,
    row_number() over(partition by center_code,schedule_code order by employ_code) erk
    from (  --转运中心拆包的迁入迁出只是判断是否有签到可以取最大，设备编码随机取的
    select center_code,
    center_name,
    schedule_code,
    schedule_name,
    schedule_start_time,
    schedule_end_time,
    create_day,
    employ_code,
    employ_name,
    collect_set(linked_method) linked_method,
    max(sign_in_time)          sign_in_time,
    max(sign_out_time)         sign_out_time,
    max(sn_code) sn_code
    from jms_dm.dm_employee_operate_info_detail
    where dt = date_add('{{ execution_date | cst_ds }}',-1)
    and linked_method = '转运中心拆包'
    and unpackflag_e = 1
    group by center_code, center_name, schedule_code, schedule_name, schedule_start_time,
    schedule_end_time, create_day, employ_code, employ_name
    )t
    )t2 on t1.center_code =t2.center_code
    and t1.schedule_code =t2.schedule_code
    and t1.rk%t2.ecnt = t2.erk-1
distribute by  dt,abs(hash(billcode))%3;
------------------------------------------------
--汇总
insert overwrite table jms_dm.dm_employee_work_out_cnt_dt partition(dt)
select
    t1.date_time ,
    t1.center_code ,
    t1.center_name,
    t1.schedule_code,
    t1.schedule_name ,
    t1.schedule_start_time,
    t1.schedule_end_time ,
    t1.employ_code,
    t1.employ_name,
    CONCAT_ws(',', t1.linked_method) scan_type,
    t1.is_sign,
    nvl(t2.operate_num,0) as work_in_cnt, --组内操作量
    t1.cnt as  work_out_cnt,  --组外操作量
    nvl(t1.cnt,0)+nvl(t2.operate_num,0) as work_cnt, --总操作量_件数
    nvl(t1.per_oper_vo,0)+nvl(t2.per_oper_vo,0) as work_bill_cnt, --总操作量_票数
    nvl(t2.per_oper_vo,0) as work_in_bill_cnt,--组内操作票数
    nvl(t1.per_oper_vo,0) as work_out_bill_cnt,--组外操作票数
    t1.date_time as dt
from
    (   --组外明细数据
        select
            date_time
             ,center_code
             ,max(center_name) center_name
             ,schedule_code
             ,max(schedule_name) schedule_name
             ,schedule_start_time
             ,schedule_end_time
             ,employ_code
             ,max(employ_name) employ_name
             ,max(is_sign) is_sign--是否有签入签出时间
             ,collect_set(linked_method) linked_method
             ,sum(nvl(is_pieces,0)) cnt --组外操作件数
             ,sum(nvl(is_ticket,0)) as per_oper_vo  --包内件+单件，票数
        from jms_dm.dm_employee_operate_billcode_detail_out_dt
        where dt=date_add('{{ execution_date | cst_ds }}',-1)
        group by
            date_time,center_code ,employ_code,schedule_code,schedule_start_time,schedule_end_time
    )t1
        left join
    (--组内操作量
        select center_code,employ_code,schedule_code,sum(operate_num) operate_num,update_time,sum(per_oper_vo) as per_oper_vo
        from  jms_dm.dm_employee_operate_num_detail
        where dt=date_add('{{ execution_date | cst_ds }}',-1)
        group by  center_code,employ_code,schedule_code,update_time
    )t2 on t1.date_time=t2.update_time and t1.center_code=t2.center_code and t1.employ_code=t2.employ_code and t1.schedule_code=t2.schedule_code
    distribute by  dt;